package org.lib.jpamysql.query;

import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
import java.util.Date;
import java.util.List;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.Future;
import java.util.stream.Stream;

import org.lib.jpamysql.query.dto.NamesDTO;
import org.lib.jpamysql.query.dto.UserPageDTO;
import org.lib.jpamysql.query.projections.FullNameOnly;
import org.lib.jpamysql.query.projections.NameOnly;
import org.lib.jpamysql.query.projections.UserSimpleVO;
import org.lib.jpamysql.query.projections.UserSummary;
import org.lib.jpamysql.query.vo.GroupbyAgeVO;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Slice;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Direction;
import org.springframework.data.domain.Sort.Order;
import org.springframework.data.repository.query.Param;
import org.springframework.format.annotation.DateTimeFormat;
import org.springframework.util.concurrent.ListenableFuture;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import lombok.extern.slf4j.Slf4j;

/**
 * 用户接口
 * @author ben
 * @date 2021-10-25 15:02:40 CST
 */
@RestController
@RequestMapping(value="/user")
@Slf4j
// 失败
//@Transactional
// 失败
//@Transactional(readOnly = true)
public class UserController {

	@Autowired
	@Qualifier("userDao")
	private UserDAO userDao;
	
	@Autowired
	@Qualifier("user2Dao")
	private User2DAO user2Dao;
	
	@Autowired
	@Qualifier("user3Dao")
	private User3DAO user3Dao;
	
	@Autowired
	@Qualifier("user4Dao")
	private User4DAO user4Dao;
	
	//
	// userDao
	
	/**
	 * 添加：临时
	 * @author ben
	 * @date 2021-10-25 15:24:13 CST
	 * @param u
	 * @return
	 */
	@PostMapping(value="/add")
	public Long add(@RequestBody User u) {
		userDao.save(u);
		log.info("添加用户：id={}", u.getId());
		return u.getId();
	}
	
	/**
	 * 分页查找
	 * @author ben
	 * @date 2021-10-25 15:24:19 CST
	 * @param dto
	 * @return
	 */
	@PostMapping(value="/page")
	public Page<User> getAllUserByPage(@RequestBody UserPageDTO dto) {
		// 1 分页
//		Pageable pageable = PageRequest.of(dto.getPageNo(), dto.getPageSize());
		// 2 分页+排序
		Pageable pageable = PageRequest.of(dto.getPageNo(), dto.getPageSize(), 
				Sort.by(Order.asc("age")));
		
		return userDao.findAll(pageable);
	}
	
	/**
	 * 排序 
	 * @author ben
	 * @date 2021-10-25 15:24:27 CST
	 * @return
	 */
	@PostMapping(value="/sort")
	public Iterable<User> getAllUserWithSort() {
		// 实体类的属性名，而不是字段名！
		Sort sort = Sort.by(Order.desc("createTime"));
		
		return userDao.findAll(sort);
	}
	
	//
	// 创建查询方法 by 查询生成器机制

	@GetMapping(value="/findByFirstName")
	List<User> findByFirstName(@RequestParam String firstName) {
		return userDao.findByFirstName(firstName);
	}
	
	@GetMapping(value="/findByLastName")
	List<User> findByLastName(@RequestParam String lastName){
		return userDao.findByLastName(lastName);
	}
	
	// And
	@GetMapping(value="/findByFirstNameAndLastName")
	List<User> findByFirstNameAndLastName(@RequestParam String firstName, @RequestParam String lastName){
		return userDao.findByFirstNameAndLastName(firstName, lastName);
	}
	
	@GetMapping(value="/findByFirstNameAndAge")
	List<User> findByFirstNameAndAge(@RequestParam String firstName, @RequestParam int age){
		return userDao.findByFirstNameAndAge(firstName, age);
	}

	// Or
	@GetMapping(value="/findByFirstNameOrLastName")
	List<User> findByFirstNameOrLastName(@RequestParam String firstName, @RequestParam String lastName){
		return userDao.findByFirstNameOrLastName(firstName, lastName);
	}
	
	// Distinct 去重
	// 无用 TODO
	@GetMapping(value="/findDistinctByFirstName")
	List<User> findDistinctByFirstName(@RequestParam String firstName){
		return userDao.findDistinctByFirstName(firstName);
	}

	// IgnoreCase 忽略大小写
	// 无用 TODO MySQL位于linux
	@GetMapping(value="/findByFirstNameIgnoreCase")
	List<User> findByFirstNameIgnoreCase(@RequestParam String firstName){
		return userDao.findByFirstNameIgnoreCase(firstName);
	}

	// ASC/DESC 排序
	@GetMapping(value="/findByFirstNameOrderByAgeDesc")
	List<User> findByFirstNameOrderByAgeDesc(@RequestParam String firstName) {
		return userDao.findByFirstNameOrderByAgeDesc(firstName);
	}
	
	// 更多查询

	// Between
	@GetMapping(value="/findByAgeBetween")
	List<User> findByAgeBetween(@RequestParam int age1, @RequestParam int age2) {
		return userDao.findByAgeBetween(age1, age2);
	}

	// GreaterThan
	@GetMapping(value="/findByAgeGreaterThan")
	List<User> findByAgeGreaterThan(@RequestParam int min) {
		return userDao.findByAgeGreaterThan(min);
	}

	// After Date类型
	// 需要配置 @DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss") 接受参数
	@GetMapping(value="/findByCreateTimeAfter")
	List<User> findByCreateTimeAfter(@RequestParam @DateTimeFormat(pattern="yyyy-MM-dd HH:mm:ss") Date start) {
		return userDao.findByCreateTimeAfter(start);
	}
	
	// Like
	@GetMapping(value="/findByFirstNameLike")
	List<User> findByFirstNameLike(@RequestParam String pattern) {
		return userDao.findByFirstNameLike(pattern);
	}

	// Containing
	@GetMapping(value="/findByFirstNameContaining")
	List<User> findByFirstNameContaining(@RequestParam String keywords) {
		return userDao.findByFirstNameContaining(keywords);
	}
	
	@GetMapping(value="/findByAddressZipCode")
	List<User> findByAddressZipCode(@RequestParam String zipCode) {
		return userDao.findByAddressZipCode(zipCode);
	}
	
	@GetMapping(value="/findByAddressZipCode2")
	List<User> findByAddressZipCode2(@RequestParam Integer type) {
		if (type == 1) {
			return userDao.findByAddressZipCodeIsNull();
		} else if (type == 2) {
			return userDao.findByAddressZipCodeNotNull();
		} else {
			throw new RuntimeException("参数错误");
		}
	}
	
	// 返回Page、Slice
	// findByAgeBetween2 findByAgeBetween3 不能共存
	
//	@GetMapping(value="/findByAgeBetween2")
//	Page<User> findByAgeBetween2(int age1, int age2, int pageNo, int pageSize) {
//		Pageable pageable = PageRequest.of(pageNo, pageSize);
//		
//		return userDao.findByAgeBetween(age1, age2, pageable);
//	}
	
	@GetMapping(value="/findByAgeBetween3")
	Slice<User> findByAgeBetween3(int age1, int age2, int pageNo, int pageSize) {
		Pageable pageable = PageRequest.of(pageNo, pageSize);
		
		return userDao.findByAgeBetween(age1, age2, pageable);
	}

	// 使用first, top
	
	@GetMapping(value="/findFirstByFirstName")
	User findFirstByFirstName(String firstName) {
		return userDao.findFirstByFirstName(firstName);
	}
	
	@GetMapping(value="/findTopByFirstName")
	User findTopByFirstName(String firstName) {
		return userDao.findTopByFirstName(firstName);
	}
	
	// First2 前2个
	@GetMapping(value="/findFirst2ByFirstName")
	List<User> findFirst2ByFirstName(String firstName) {
		return userDao.findFirst2ByFirstName(firstName);
	}
	
	// 返回Stream
	
	@GetMapping(value="/findAllByCustomQueryAndStream")
	// 失败
//	@Transactional
	// 失败
//	@Transactional(transactionManager = "transactionManager")
	List<User> findAllByCustomQueryAndStream() {
		return findAllByCustomQueryAndStreamInner();
	}
	
	// 失败
//	@Transactional(readOnly = true)
	private List<User> findAllByCustomQueryAndStreamInner() {
		List<User> ret = new ArrayList<User>();
		
		Stream<User> stream = null;
		try {
			stream = userDao.findAllByCustomQueryAndStream();
			System.out.println("stream=" + stream + ", count=" + stream.count());
			stream.forEach(u->{
				ret.add(u);
			});
		} catch (Exception e) {
			log.error("发生异常：e=", e);
		} finally {
			stream.close();
		}
		
		return ret;
	}

	// 异步查询结果
	
	@GetMapping(value="/findOneByAge")
	boolean findOneByAge(int age) {
		Future<User> fu = userDao.findOneByAge(age);
		
		log.info("1");
		User u = null;
		try {
			u = fu.get();
		} catch (InterruptedException | ExecutionException e) {
			e.printStackTrace();
			return false;
		}
		log.info("2 u=" + u);
		
		return true;
	}
	
	@GetMapping(value="/findOneByFirstName")
	boolean findOneByFirstName(String firstName) {
		CompletableFuture<User> cfu = userDao.findOneByFirstName(firstName);
		
		log.info("1");
		User u = null;
		try {
			u = cfu.get();
		} catch (InterruptedException | ExecutionException e) {
			e.printStackTrace();
			return false;
		}
		log.info("2 u=" + u);
		
		return true;
	}
	
	@GetMapping(value="/findOneByLastName")
	boolean findOneByLastName(String lastName) {
		ListenableFuture<User> lfu = userDao.findOneByLastName(lastName);

		log.info("1");
		User u = null;
		try {
			u = lfu.get();
		} catch (InterruptedException | ExecutionException e) {
			e.printStackTrace();
		}
		log.info("2 u=" + u);
		
		return true;
	}
	
	//
	// user2Dao
	
	@GetMapping("findByLastName2")
	List<NameOnly> findByLastName2(String lastName) {
		return user2Dao.findByLastName(lastName);
	}
	
	@GetMapping("findDistinctByLastName2")
	List<NameOnly> findDistinctByLastName2(String lastName) {
		return user2Dao.findDistinctByLastName(lastName);
	}
	
	@GetMapping("findByAge2")
	List<UserSummary> findByAge2(int age) {
		return user2Dao.findByAge(age);
	}
	
	@GetMapping("findFirstByAge2")
	List<FullNameOnly> findFirstByAge2(int age) {
		return user2Dao.findFirstByAge(age);
	}
	
	@GetMapping("findByAgeGreaterThan2")
	List<UserSimpleVO> findByAgeGreaterThan2(int age) {
		return user2Dao.findByAgeGreaterThan(age);
	}
	
	@GetMapping("findByLastNameDynamic")
	Collection<?> findByLastNameDynamic(String lastName, int type) {
		switch(type) {
		case 1:
			return user2Dao.findByLastName(lastName, NameOnly.class);
		case 2:
			return user2Dao.findByLastName(lastName, FullNameOnly.class);
		case 3:
			return user2Dao.findByLastName(lastName, UserSimpleVO.class);
		default:
			return Collections.EMPTY_LIST;
		}
	}
	
	//
	// 注解式查询方法 @Query
	
	@GetMapping(value="/3/find1User")
	User find1User(@RequestParam String firstName) {
		return user3Dao.find1User(firstName);
	}
	
	@GetMapping(value="/3/findUserList")
	List<User> findUserList(String firstName, String field) {
		Sort sort = Sort.by(Direction.DESC, field);
		return user3Dao.findUserList(firstName, sort);
	}

	@GetMapping(value="/3/findLikeByFirstName")
	List<User> findLikeByFirstName(String keywords) {
		return user3Dao.findLikeByFirstName(keywords);
	}
	
	@GetMapping(value="/3/pageUserGreaterThan")
	Page<User> pageUserGreaterThan(@RequestParam Integer age, 
			@RequestParam(defaultValue = "0") Integer pageNo, @RequestParam(defaultValue = "2") Integer pageSize) {
		Pageable pageable = PageRequest.of(pageNo, pageSize);
		return user3Dao.pageUserGreaterThan(age, pageable);
	}
	
	@GetMapping(value="/3/findByNames")
	List<User> findByNames(@RequestParam String name2, @RequestParam String name1) {
		return user3Dao.findByNames(name2, name1);
	}
	
	@GetMapping(value="/3/findByNamesDto")
	List<User> findByNamesDto(@RequestParam String name2, @RequestParam String name1) {
		NamesDTO dto = new NamesDTO();
		dto.setName1(name1);
		dto.setName2(name2);
		return user3Dao.findByNamesDto(dto);
	}
	
	@GetMapping(value="/3/groupbyAge")
	List<Object[]> groupbyAge(@RequestParam Long nums) {
		return user3Dao.groupbyAge(nums);
	}
	
	@GetMapping(value="/3/findBySpel")
	List<User> findBySpel(@RequestParam Integer age) {
		return user3Dao.findBySpel(age);
	}
	
	// 原生SQL
	
	@GetMapping(value="/3/find1UserSql")
	User find1UserSql(@RequestParam String firstName) {
		return user3Dao.find1UserSql(firstName);
	}

	// 错误方式：启动失败
//	@GetMapping(value="/3/findUserListSql/0")
//	List<User> findUserListSql(String firstName, String field) {
//		Sort sort = Sort.by(Direction.DESC, field);
//		return user3Dao.findUserListSql(firstName, sort);
//	}
	
	// 正确方式
	// 不能倒序排列
	@GetMapping(value="/3/findUserListSql1")
	List<User> findUserListSql(@RequestParam String firstName, @RequestParam String sort, @RequestParam String order) {
//		return user3Dao.findUserListSql(firstName, sort, order);
		
		String newsort = sort + " " + order;
		return user3Dao.findUserListSql(firstName, newsort);
//		return user3Dao.findUserListSql(firstName, sort);
	}
	
	@GetMapping(value="/3/findLikeByFirstNameSql")
	List<User> findLikeByFirstNameSql(@RequestParam String keywords) {
		return user3Dao.findLikeByFirstNameSql(keywords);
	}
	
	@GetMapping(value="/3/pageUserGreaterThanSql")
	Page<User> pageUserGreaterThanSql(Integer age, 
			@RequestParam(defaultValue = "0") Integer pageNo, @RequestParam(defaultValue = "2") Integer pageSize) {
		Pageable pageable = PageRequest.of(pageNo, pageSize);
		return user3Dao.pageUserGreaterThanSql(age, pageable);
	}

	@GetMapping(value="/3/findByNamesSql")
	List<User> findByNamesSql(@RequestParam String name2, @RequestParam String name1) {
		return user3Dao.findByNamesSql(name2, name1);
	}

	@GetMapping(value="/3/findByNamesDtoSql")
	List<User> findByNamesDtoSql(@RequestParam String name2, @RequestParam String name1) {
		NamesDTO dto = new NamesDTO();
		dto.setName1(name1);
		dto.setName2(name2);
		return user3Dao.findByNamesDtoSql(dto);
	}
	
	@GetMapping(value="/3/findInNamedQuery")
	List<User> findInNamedQuery(@RequestParam String firstName) {
		return user3Dao.findInNamedQuery(firstName);
	}
	
	// 使用存储过程

	@GetMapping(value="/3/explicitlyNamedPlus1inout")
	Integer explicitlyNamedPlus1inout(@RequestParam Integer arg) {
		return user3Dao.explicitlyNamedPlus1inout(arg);
	}
	
	@GetMapping(value="/3/plus1inout")
	Integer plus1inout(@RequestParam Integer arg) {
		return user3Dao.plus1inout(arg);
	}
	
	@GetMapping(value="/3/entityAnnotatedCustomNamedProcedurePlus1io")
	Integer entityAnnotatedCustomNamedProcedurePlus1io(@RequestParam Integer arg) {
		return user3Dao.entityAnnotatedCustomNamedProcedurePlus1io(arg);
	}
	
	//更改
	// user4Dao
	
	@GetMapping(value="/updateAge")
	Integer updateAge(@RequestParam Long id, @RequestParam Integer newAge) {
		log.info("更新User：id={}", id);
		return user4Dao.updateAge(id, newAge);
	}
	
	@GetMapping(value="/deleteUser")
	Integer deleteUser(@RequestParam Long id) {
		log.info("删除User：id={}", id);
		return user4Dao.deleteUser(id);
	}
	
}
